IF EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID('PaintSubjectBatchUpdate','P'))
    DROP PROCEDURE PaintSubjectBatchUpdate
GO
/*
 * Designer:     Kevin
 * Description:    
 * Created:      03/10/2011
 * History:
 * =============================================================================
 * Author      DateTime        Alter Description
 * =============================================================================
 */

CREATE PROCEDURE PaintSubjectBatchUpdate
(
	@PaintingId int,
	@Subjects nvarchar(max)
)
AS
BEGIN

	if @Subjects is null or @Subjects=''
	begin
		return
	end
	
	declare @TempSubject table(
		SubjectId int,
		SubjectValue nvarchar(100)
	)

	insert into @TempSubject(
		SubjectValue
	)
	select Item as SubjectValue
	from FunStringSplit(@Subjects, ',')
	
	insert into Subject(
		[value]
	)
	select SubjectValue
	from @TempSubject TempSubject
	left join Subject on TempSubject.SubjectValue=Subject.[value]
	where Subject.[value] is null
	
	update TempSubject
	set 
		SubjectId=Subject.Subject_id
	from 
		@TempSubject TempSubject
		inner join Subject on TempSubject.SubjectValue=Subject.[value]
				
	update Subject
	set count_of_paint=count_of_paint+1
	from Subject 
	inner join @TempSubject TempSubject on Subject.Subject_Id = TempSubject.SubjectId
	left join painting_Subject on TempSubject.SubjectId=painting_Subject.Subject_id and painting_Subject.painting_id=@PaintingId
	where painting_Subject.Subject_id is null
	
	insert into painting_Subject (
		painting_id,
		Subject_id
	)
	select 
		@PaintingId,
		TempSubject.SubjectId
	from @TempSubject TempSubject
	left join painting_Subject on TempSubject.SubjectId=painting_Subject.Subject_id and painting_Subject.painting_id=@PaintingId
	where painting_Subject.Subject_id is null
	
	update Subject
	set count_of_paint=count_of_paint-1
	from Subject 
	inner join painting_Subject on Subject.subject_id=painting_Subject.Subject_id 
	left join @TempSubject TempSubject on TempSubject.SubjectId=painting_Subject.Subject_id 
	where 
		TempSubject.SubjectId is null
		and painting_Subject.painting_id=@PaintingId
	
	delete from painting_Subject
	from painting_Subject
	left join @TempSubject TempSubject on TempSubject.SubjectId=painting_Subject.Subject_id 
	where 
		TempSubject.SubjectId is null
		and painting_Subject.painting_id=@PaintingId
END
GO